<?php
$startIndex = $_POST['startIndex'];
$perPage = $_POST['perPage'];
include_once "conn.php";
$main = new Bbs();
$sql = "SELECT
	M.id,
	title,
	M.nickname MainNickName,
	posttime,
  istop,
	CASE
WHEN replyNum IS NULL THEN
	0
ELSE
	replyNum
END AS replyNum,
 hits,
 CASE
WHEN S.lastreplyposttime IS NULL THEN
	posttime
ELSE
	S.lastreplyposttime
END AS lastreplyposttime,
	CASE
WHEN istop = 1 THEN
	NOW()
END AS NEWTIME,
 CASE
WHEN S.nickname IS NULL THEN
	\"暂无回复\"
ELSE
	S.nickname
END AS SubNickName
FROM
	(
		SELECT
			M.id,
			title,
			posttime,
			hits,
			U.nickname,
      istop
		FROM
			maintopic M,
			userinfo U
		WHERE
			U.id = M.userid
	) M
LEFT JOIN (
	SELECT
		S.posttime lastreplyposttime,
		U.nickname,
		maintopicid,
		replyNum,
		S.id
	FROM
		(
			SELECT
				A.id,
				posttime,
				maintopicid,
				userid,
				replyNum
			FROM
				subtopic A,
				(
					SELECT
						max(id) id,
						COUNT(1) replyNum
					FROM
						subtopic
					GROUP BY
						maintopicid
				) B
			WHERE
				A.id = B.id
		) S,
		userinfo U
	WHERE
		U.id = S.userid
	ORDER BY
		S.id DESC
) S ON S.maintopicid = M.id
ORDER BY
	NEWTIME DESC,lastreplyposttime DESC,M.id DESC";
$result = $main->query($sql,0);
if($result['error']){
    $a['error'] = 1;
    $a['errMsg'] = "查询出错";
    echo json_encode($a);
}
else{
    $a['total'] = $result['total'];
    $sql .= " limit $startIndex, $perPage";
    $info = $main->query($sql,1);
    if($result['error']){
        $a['error'] = 1;
        $a['errMsg'] = "查询出错";
        echo json_encode($a);
        exit;
    }
    else{
        $a['error'] = 0;
        $a['data'] = $info['result'];
        echo json_encode($a);
    }
}